using System.Data;
using Oracle.ManagedDataAccess.Client;
using Serilog;
using 后端服务.Models;

namespace 后端服务.Services;

/// <summary>
/// 提供原生SQL查询服务
/// </summary>
public class SqlQueryService
{
    private readonly string _connectionString;
    private readonly SqlLoggerService _logger;
    
    public SqlQueryService(IConfiguration configuration, SqlLoggerService logger)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection")
            ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");
        _logger = logger;
    }

    /// <summary>
    /// 执行查询并返回动态结果集
    /// </summary>
    /// <param name="sql">SQL查询语句</param>
    /// <param name="parameters">查询参数</param>
    /// <returns>动态查询结果</returns>
    public async Task<SqlQueryResult> QueryDynamicAsync(string sql, Dictionary<string, object>? parameters = null)
    {
        var startTime = DateTime.UtcNow;
        try
        {
            Log.Information($"Executing SQL: {sql}");
            if (parameters?.Count > 0)
            {
                Log.Information("Parameters: {@Parameters}", parameters);
            }

            using var connection = new OracleConnection(_connectionString);
            await connection.OpenAsync();
            
            using var command = new OracleCommand(sql, connection);
            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    command.Parameters.Add(new OracleParameter(param.Key, param.Value));
                }
            }

            using var reader = await command.ExecuteReaderAsync();
            var result = await SqlQueryResult.FromDataReaderAsync(reader);

            var duration = (long)(DateTime.UtcNow - startTime).TotalMilliseconds;
            _logger.LogQuery(sql, parameters, duration, true);
            Log.Information($"Query completed successfully. Returned {result.Rows.Count} rows.");
            return result;
        }
        catch (Exception ex)
        {
            var duration = (long)(DateTime.UtcNow - startTime).TotalMilliseconds;
            _logger.LogQuery(sql, parameters, duration, false, ex.Message);
            Log.Error(ex, "Error executing SQL query");
            throw;
        }
    }

    /// <summary>
    /// 执行查询并返回结果集
    /// </summary>
    /// <typeparam name="T">返回结果的类型</typeparam>
    /// <param name="sql">SQL查询语句</param>
    /// <param name="parameters">查询参数</param>
    /// <returns>查询结果列表</returns>
    public async Task<List<T>> QueryAsync<T>(string sql, Dictionary<string, object>? parameters = null)
    {
        var startTime = DateTime.UtcNow;
        try
        {
            Log.Information($"Executing SQL: {sql}");
            if (parameters?.Count > 0)
            {
                Log.Information("Parameters: {@Parameters}", parameters);
            }

            using var connection = new OracleConnection(_connectionString);
            await connection.OpenAsync();
            
            using var command = new OracleCommand(sql, connection);
            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    command.Parameters.Add(new OracleParameter(param.Key, param.Value));
                }
            }

            using var reader = await command.ExecuteReaderAsync();
            var results = new List<T>();
            var properties = typeof(T).GetProperties().ToDictionary(p => p.Name.ToUpper(), p => p);
            var dynamicProperties = new Dictionary<string, object>();
            
            while (await reader.ReadAsync())
            {
                var item = Activator.CreateInstance<T>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var columnName = reader.GetName(i).ToUpper();
                    if (properties.TryGetValue(columnName, out var property))
                    {
                        if (reader[i] != DBNull.Value)
                        {
                            try
                            {
                                var value = Convert.ChangeType(reader[i], Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
                                property.SetValue(item, value);
                            }
                            catch (Exception ex)
                            {
                                Log.Warning(ex, "Error setting property {PropertyName} for type {TypeName}. Value: {Value}", property.Name, typeof(T).Name, reader[i]);
                            }
                        }
                    }
                    else
                    {
                        // 存储额外的列（如 TotalCount）
                        if (reader[i] != DBNull.Value)
                        {
                            dynamicProperties[columnName] = reader[i];
                            // 使用动态类型扩展对象
                            var dynProp = typeof(T).GetProperty(columnName, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
                            if (dynProp != null)
                            {
                                try
                                {
                                    var value = Convert.ChangeType(reader[i], Nullable.GetUnderlyingType(dynProp.PropertyType) ?? dynProp.PropertyType);
                                    dynProp.SetValue(item, value);
                                }
                                catch (Exception ex)
                                {
                                    Log.Warning(ex, "Error setting dynamic property {PropertyName} for type {TypeName}. Value: {Value}", dynProp.Name, typeof(T).Name, reader[i]);
                                }
                            }
                        }
                    }
                }
                results.Add(item);
            }

            var duration = (long)(DateTime.UtcNow - startTime).TotalMilliseconds;
            _logger.LogQuery(sql, parameters, duration, true);
            Log.Information($"Query completed successfully. Returned {results.Count} rows.");
            return results;
        }
        catch (Exception ex)
        {
            var duration = (long)(DateTime.UtcNow - startTime).TotalMilliseconds;
            _logger.LogQuery(sql, parameters, duration, false, ex.Message);
            Log.Error(ex, "Error executing SQL query");
            throw;
        }
    }

    /// <summary>
    /// 执行非查询SQL语句（如INSERT、UPDATE、DELETE）
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="parameters">参数</param>
    /// <returns>受影响的行数</returns>
    public async Task<int> ExecuteAsync(string sql, Dictionary<string, object>? parameters = null)
    {
        var startTime = DateTime.UtcNow;
        try
        {
            Log.Information($"Executing SQL: {sql}");
            if (parameters?.Count > 0)
            {
                Log.Information("Parameters: {@Parameters}", parameters);
            }

            using var connection = new OracleConnection(_connectionString);
            await connection.OpenAsync();
            
            using var command = new OracleCommand(sql, connection);
            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    command.Parameters.Add(new OracleParameter(param.Key, param.Value));
                }
            }

            var result = await command.ExecuteNonQueryAsync();
            var duration = (long)(DateTime.UtcNow - startTime).TotalMilliseconds;
            _logger.LogQuery(sql, parameters, duration, true);
            Log.Information($"Execute completed successfully. Affected {result} rows.");
            return result;
        }
        catch (Exception ex)
        {
            var duration = (long)(DateTime.UtcNow - startTime).TotalMilliseconds;
            _logger.LogQuery(sql, parameters, duration, false, ex.Message);
            Log.Error(ex, "Error executing SQL command");
            throw;
        }
    }

    /// <summary>
    /// 执行查询并返回两个相关联的实体
    /// </summary>
    /// <typeparam name="T1">第一个实体类型</typeparam>
    /// <typeparam name="T2">第二个实体类型</typeparam>
    /// <param name="sql">SQL查询语句</param>
    /// <param name="parameters">查询参数</param>
    /// <returns>包含两个关联实体的元组列表</returns>
    public async Task<List<(T1, T2)>> QueryAsync<T1, T2>(string sql, Dictionary<string, object>? parameters = null)
    {
        var startTime = DateTime.UtcNow;
        try
        {
            Log.Information($"Executing SQL: {sql}");
            if (parameters?.Count > 0)
            {
                Log.Information("Parameters: {@Parameters}", parameters);
            }

            using var connection = new OracleConnection(_connectionString);
            await connection.OpenAsync();
            
            using var command = new OracleCommand(sql, connection);
            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    command.Parameters.Add(new OracleParameter(param.Key, param.Value));
                }
            }

            using var reader = await command.ExecuteReaderAsync();
            var results = new List<(T1, T2)>();
            
            while (await reader.ReadAsync())
            {
                var item1 = Activator.CreateInstance<T1>();
                var item2 = Activator.CreateInstance<T2>();
                
                // 填充第一个实体
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var columnName = reader.GetName(i);
                    var property = typeof(T1).GetProperty(columnName);
                    if (property != null && reader[i] != DBNull.Value)
                    {
                        try
                        {
                            property.SetValue(item1, Convert.ChangeType(reader[i], property.PropertyType));
                        }
                        catch { /* 忽略类型转换错误 */ }
                    }
                }
                
                // 填充第二个实体
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var columnName = reader.GetName(i);
                    var property = typeof(T2).GetProperty(columnName);
                    if (property != null && reader[i] != DBNull.Value)
                    {
                        try
                        {
                            property.SetValue(item2, Convert.ChangeType(reader[i], property.PropertyType));
                        }
                        catch { /* 忽略类型转换错误 */ }
                    }
                }
                
                results.Add((item1, item2));
            }

            var duration = (long)(DateTime.UtcNow - startTime).TotalMilliseconds;
            _logger.LogQuery(sql, parameters, duration, true);
            Log.Information($"Query completed successfully. Returned {results.Count} rows.");
            return results;
        }
        catch (Exception ex)
        {
            var duration = (long)(DateTime.UtcNow - startTime).TotalMilliseconds;
            _logger.LogQuery(sql, parameters, duration, false, ex.Message);
            Log.Error(ex, "Error executing SQL query");
            throw;
        }
    }

    /// <summary>
    /// 执行查询并返回第一行第一列的值
    /// </summary>
    /// <typeparam name="T">返回值类型</typeparam>
    /// <param name="sql">SQL查询语句</param>
    /// <param name="parameters">查询参数</param>
    /// <returns>查询结果</returns>
    public async Task<T?> ExecuteScalarAsync<T>(string sql, Dictionary<string, object>? parameters = null)
    {
        var startTime = DateTime.UtcNow;
        try
        {
            Log.Information($"Executing SQL: {sql}");
            if (parameters?.Count > 0)
            {
                Log.Information("Parameters: {@Parameters}", parameters);
            }

            using var connection = new OracleConnection(_connectionString);
            await connection.OpenAsync();
            
            using var command = new OracleCommand(sql, connection);
            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    command.Parameters.Add(new OracleParameter(param.Key, param.Value));
                }
            }

            var result = await command.ExecuteScalarAsync();
            var duration = (long)(DateTime.UtcNow - startTime).TotalMilliseconds;
            _logger.LogQuery(sql, parameters, duration, true);
            Log.Information($"ExecuteScalar completed successfully. Result: {result}");
            
            if (result == DBNull.Value)
                return default;
                
            return (T)Convert.ChangeType(result, typeof(T));
        }
        catch (Exception ex)
        {
            var duration = (long)(DateTime.UtcNow - startTime).TotalMilliseconds;
            _logger.LogQuery(sql, parameters, duration, false, ex.Message);
            Log.Error(ex, "Error executing SQL scalar query");
            throw;
        }
    }
}